package service;

import java.util.List;

import bean.FirstCategory;
import bean.FirstCategoryRowMapper;
import bean.Goods;
import bean.GoodsImg;
import bean.GoodsImgRowMapper;
import bean.GoodsRowMapper;
import bean.SecondCategory;
import bean.SecondCategoryRowMapper;
import bean.ThirdCategory;
import bean.ThirdCategoryRowMapper;
import bean.admin;
import bean.adminRowMapper;
import common.BaseDAO;

public class adminGoodsDAO extends BaseDAO{
	public List<Goods> queryAllGoods(int pagesize,int viewPage){
		int offset=pagesize*(viewPage-1);
			String sql = "select * from goods_info where ifexist=1 order by goodsid limit ?,?";
			return this.query(sql, new GoodsRowMapper(),offset,pagesize);
	}
	//获取所有商品记录
	public int listAll(){
		String sql="select count(*) from goods_info where ifexist=1 order by goodsid";
		return this.getCount(sql);
	}
	//通过商品ID获取商品图片
	public List<GoodsImg> getImg(int goodsid) {
		String sql="select * from goods_img where goodsid=?";
		return this.query(sql, new GoodsImgRowMapper(), goodsid);
	}
	//通过商品ID获取商品记录
		public List<Goods> getGoods(String goodsid) {
			String sql="select * from goods_info where goodsid=?";
			return this.query(sql, new GoodsRowMapper(), goodsid);
		}
	//根据三级分类id在三级分类商品表查询三级分类商品
	public List<ThirdCategory> queryByTcid(int tcid){
		String sql="select * from third_category where third_categoryid=? and ifexist=1";
		return this.query(sql, new ThirdCategoryRowMapper(), tcid);
	}
	//根据商品ID修改商品上架
	public void onsale(String goodsid) {
			String sql="update goods_info set ifonsale=1 where goodsid=?";
			this.update(sql,goodsid);	
	}
	//根据商品ID修改商品下架
		public void downsale(String goodsid) {
				String sql="update goods_info set ifonsale=0 where goodsid=?";
				this.update(sql,goodsid);	
		}

	//根据商品ID修改(伪)删除商品
			public void delete(String goodsid) {
					String sql="update goods_info set ifexist=0 where goodsid=?";
					this.update(sql,goodsid);	
			}
			//更新商品价格方法
			public void updatePrice(Double price, String goodsid) {
				String sql="update goods_info set price=? where goodsid=?";
				this.update(sql,price,goodsid);	
		}
			//更新商品成本方法
			public void updateCost(Double cost, String goodsid) {
				String sql="update goods_info set cost=? where goodsid=?";
				this.update(sql,cost,goodsid);	
		}
			//更新商品优惠方法
			public void updateDiscount(Double discount_price, String goodsid) {
				String sql="update goods_info set discount_price=? where goodsid=?";
				this.update(sql,discount_price,goodsid);	
		}
			//更新商品库存方法
			public void updateCount(Double count, String goodsid) {
				String sql="update goods_info set count=? where goodsid=?";
				this.update(sql,count,goodsid);	
		}
			//更新商品运费方法
			public void updatePostfee(Double post_fee, String goodsid) {
				String sql="update goods_info set post_fee=? where goodsid=?";
				this.update(sql,post_fee,goodsid);	
		}
			//根据三级分类id在商品表查询商品信息
			public List<Goods> queryGoodsByTcid(int tcid){
				
				String sql="select * from goods_info where third_categoryid=?";
				return this.query(sql, new GoodsRowMapper(), tcid);
			}
			//根据搜索词在商品名/关键字/中查询三级分类id
			public List<ThirdCategory> queryBySearchWord(String searchWord){
				
				String sql="select * from third_category "
						+ "where second_categoryid in "
						+ "(select second_categoryid from second_category"
						+ " where first_categoryid in"
						+ "(select first_categoryid from first_category "
						+ "where locate(?,name)>0)) "
						+ "or second_categoryid in "
						+ "(select second_categoryid from second_category "
						+ "where locate(?,name)>0) "
						+ "or locate(?,name)>0 or locate(?,keyword)>0 "
						+ "group by third_categoryid "
						;
				List<ThirdCategory> list=this.query(sql, new ThirdCategoryRowMapper(), searchWord,searchWord,searchWord, searchWord);
				
				  return list;
				
				
			}	
			//查询无货商品
			public List<Goods> queryGoodsByCount0(){
							
							String sql="select * from goods_info where count=0";
							return this.query(sql, new GoodsRowMapper());
			}
			public List<Goods> queryGoodsByCount10(){
				
				String sql="select * from goods_info where count between 1 and 9";
				return this.query(sql, new GoodsRowMapper());
}
			public List<Goods> queryGoodsByCount100(){
				
				String sql="select * from goods_info where count between 10 and 100";
				return this.query(sql, new GoodsRowMapper());
}
			public List<Goods> queryGoodsByCount101(){
				
				String sql="select * from goods_info where count between 101 and 999999";
				return this.query(sql, new GoodsRowMapper());
}
	//根据商品是否上架查询		
			public List<Goods> queryGoodsByIfonsale(String ifonsale){
				
				String sql="select * from goods_info where ifonsale=?";
				return this.query(sql, new GoodsRowMapper(),ifonsale);
			}
			//根据商品标签查询
			public List<ThirdCategory> queryGoodsByLaber(String label){
				
				String sql="select * from third_category where label=?";
				return this.query(sql, new ThirdCategoryRowMapper(),label);
			}
			//根据商品价格区间查询
			public List<Goods> queryGoodsByPrice(String priceDown ,String priceUp){
				
				String sql="select * from goods_info where price between ? and ?";
				return this.query(sql, new GoodsRowMapper(),priceDown,priceUp);
			}
			public List<Goods> queryGoodsByPriceDown(String priceDown ){
				
				String sql="select * from goods_info where price between ? and 9999999";
				return this.query(sql, new GoodsRowMapper(),priceDown);
			}
			public List<Goods> queryGoodsByPriceUP(String priceUp ){
				
				String sql="select * from goods_info where price between 0 and ?";
				return this.query(sql, new GoodsRowMapper(),priceUp);
			}
			
			//查询所有一级分类
			public List<FirstCategory> queryAllFirst(){
				String sql="select * from first_category order by first_categoryid";
				return this.query(sql, new FirstCategoryRowMapper());
			}
			//通过一级分类ID查询二级分类
			public List<SecondCategory> querySecondID(int first_categoryid){
				String sql="select * from second_category where first_categoryid=?";
				return this.query(sql, new SecondCategoryRowMapper(),first_categoryid);
			}
			//通过2级分类ID查询3级分类
			public List<ThirdCategory> queryThird(int second_categoryid){
				String sql="select * from third_category where second_categoryid=?";
				return this.query(sql, new ThirdCategoryRowMapper(),second_categoryid);
			}
			
			//获取所有三级分类记录
			public int listThird(){
				String sql="select count(*) from third_category  order by third_categoryid";
				return this.getCount(sql);
			}
			
}